﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using HRM.DAL.DAO;

namespace HRM.DAL.Gateway
{
    public class UserGateway:DBGateway
    {
        public bool HasThisUserAlreadyExsit(int empId, User user)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = "select EmpId from tblUser where EmpId='"+empId+"' or UserName='"+user.UserName+"'";
                SqlDataReader reader = CommandObj.ExecuteReader();
                while (reader.Read())
                {
                    return true;
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Error in Database !",ex);
            }
            finally
            {
                ConnectionObj.Close();
            }
            return false;
        }

        public string CreateNewUser(User user, int empId)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = string.Format("insert into tblUser values(@empId,@userName,@pass,@role)");
                CommandObj.Parameters.Clear();
                CommandObj.Parameters.Add("@empId", SqlDbType.Int).Value = empId;
                CommandObj.Parameters.Add("@userName", SqlDbType.VarChar).Value = user.UserName;
                CommandObj.Parameters.Add("@pass", SqlDbType.VarChar).Value = user.Password;
                CommandObj.Parameters.Add("@role", SqlDbType.Int).Value = user.Role.Id;
                CommandObj.ExecuteNonQuery();
                return "Account Created Successfully";
            }
            catch (Exception ex)
            {
                throw new Exception("Error in Data Base during Saving account Information",ex);
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public bool ChangePassword(User user, int empId)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = string.Format("Update tblUser set Password=@pass where EmpId='" + empId + "'");
                CommandObj.Parameters.Clear();
                CommandObj.Parameters.Add("@pass", SqlDbType.VarChar).Value = user.Password;
                CommandObj.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                throw new Exception("Error in Data Base during Changing Password", ex);
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public bool ChangeUsernamePassword(User user,int empId)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = string.Format("Update tblUser set Username=@username,Password=@pass where EmpId='" + empId + "'");
                CommandObj.Parameters.Clear();
                CommandObj.Parameters.Add("@username", SqlDbType.VarChar).Value = user.UserName;
                CommandObj.Parameters.Add("@pass", SqlDbType.VarChar).Value = user.Password;
                CommandObj.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                throw new Exception("Error in Data Base during Changing Password", ex);
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public bool ResetAccounts(int empId)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = string.Format("Delete from tblUser where EmpId='" + empId + "'");
                CommandObj.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                throw new Exception("Error in Data Base during Reseting Accounts", ex);
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public User CheckAuthentication(User user)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = @"select tblUser.EmpId,tblCode.CodeName,tblUser.UserName,tblUser.Password,tblUser.Role from tblUser,tblCode,tblEmployees 
                where tblUser.EmpId=tblEmployees.EmployeeId and tblEmployees.Code_CodeID=tblCode.Id and tblUser.UserName='" +user.UserName+"' and tblUser.Password='"+user.Password+"'";
                SqlDataReader reader = CommandObj.ExecuteReader();
                User aUser=new User();
                while (reader.Read())
                {
                    aUser.Id = Convert.ToInt32(reader[0]);
                    Code code=new Code();
                    Employee employee=new Employee();
                    code.CodeName = reader[1].ToString();
                    employee.EmpCode = code;
                    aUser.Employee = employee;
                    aUser.UserName = reader[2].ToString();
                    aUser.Password = reader[3].ToString();
                    Role role=new Role();
                    role.Id = Convert.ToInt32(reader[4]);
                    aUser.Role = role;
                }
                return aUser;
            }
            catch (Exception ex)
            {
                throw new Exception("Error in Database !", ex);
            }
            finally
            {
                ConnectionObj.Close();
            }
         
        }

        public bool EditAccount(User user, int empId)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = string.Format("Update tblUser set Role=@role where EmpId='" + empId + "'");
                CommandObj.Parameters.Clear();
                CommandObj.Parameters.Add("@role", SqlDbType.VarChar).Value = user.Role.Id;
                CommandObj.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                throw new Exception("Error in Data Base during Changing User Role", ex);
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public User GetAccountsDetails(int empId)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = "select * from tblUser where EmpId='"+empId+"'";
                SqlDataReader reader = CommandObj.ExecuteReader();
                User aUser = new User();
                while (reader.Read())
                {
                    aUser.Id = Convert.ToInt32(reader[0]);
                    Employee employee = new Employee();
                    employee.Id = Convert.ToInt32(reader[1]);
                    aUser.Employee = employee;
                    aUser.UserName = reader[2].ToString();
                    aUser.Password = reader[3].ToString();
                    Role role = new Role();
                    role.Id = Convert.ToInt32(reader[4]);
                    aUser.Role = role;
                }
                return aUser;
            }
            catch (Exception ex)
            {
                throw new Exception("Error in Database !", ex);
            }
            finally
            {
                ConnectionObj.Close();
            }
        }
    }
}